package com.poi;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POITool {
	public static Workbook getBookWrite() {
		Workbook bw = null;
		try {
			bw = new XSSFWorkbook();
		} catch (Exception e) {
			bw = new HSSFWorkbook();
		}
		return bw;
	}
	public static Workbook getBookRead(InputStream is) {
		Workbook br = null;
		try {
			br = new XSSFWorkbook(is);
		} catch (Exception e) {
			try {
				br = new HSSFWorkbook(is);
			} catch (IOException e1) {
				e1.printStackTrace();
			}
		}
		return br;
	}

	private static File getFile(String filePath, boolean createNew) {
		File file = null;
		file = new File(filePath);
		if (file.exists() && file.canExecute()) {
			if (createNew) {
				file.delete();
				try {
					file.getParentFile().mkdirs();
					file.createNewFile();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}else{
			try {
				file.getParentFile().mkdirs();
				file.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return file;
	}

	public static void writeToFile(String filePath) {
		Workbook wb = getBookWrite();
		Sheet sheet = wb.createSheet("我的测试文档");
		for (int i = 0; i < 2 + 12 + 5; i++) {
			org.apache.poi.ss.usermodel.Row row = sheet.createRow(i);
			for (int rowId = 0; rowId < 2 + 13 * 12 + 5; rowId++) {
				row.createCell(rowId);
			}
		}
		for (int i = 0; i < 13; i++) {
			sheet.addMergedRegion(new CellRangeAddress(0, 2 + i, 0,	2 + i + 12));
			sheet.getRow(0).getCell(2 + i).setCellValue((i + 1) + "月");
		}
		sheet.addMergedRegion(new CellRangeAddress(0, 2 + 13, 0, 2 + 13 + 5));
		sheet.getRow(0).getCell(2 + 13).setCellValue("用药情况");
		for (int i = 0; i < 26; i++) {
			sheet.addMergedRegion(new CellRangeAddress(1, 2 + i, 1, 2 + i + 6));
			if (i % 2 == 0) {
				sheet.getRow(1).getCell(i).setCellValue("总数");
			} else {
				sheet.getRow(1).getCell(i).setCellValue("活体");
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(1, 2 + 12 * 13, 1,2 + 12 * 13 + 5));
		sheet.getRow(1).getCell(2 + 13).setCellValue("比例");
		sheet.getRow(2).getCell(0).setCellValue("区域");
		sheet.getRow(2).getCell(1).setCellValue("医院");
		for (int i = 0; i < 13 * 2; i++) {
			// System.out.println("i:"+i);
			sheet.getRow(2).getCell(2 + 6 * i).setCellValue("PRG");
			sheet.getRow(2).getCell(2 + 6 * i + 1).setCellValue("CsA");
			sheet.getRow(2).getCell(2 + 6 * i + 2).setCellValue("海正TAC");
			sheet.getRow(2).getCell(2 + 6 * i + 3).setCellValue("华东TAC");
			sheet.getRow(2).getCell(2 + 6 * i + 4).setCellValue("Siso");
			sheet.getRow(2).getCell(2 + 6 * i + 5).setCellValue("Total");
		}
		sheet.getRow(2).getCell(2 + 6 * 26).setCellValue("PRG");
		sheet.getRow(2).getCell(2 + 6 * 26 + 1).setCellValue("CsA");
		sheet.getRow(2).getCell(2 + 6 * 26 + 2).setCellValue("海正TAC");
		sheet.getRow(2).getCell(2 + 6 * 26 + 3).setCellValue("华东TAC");
		sheet.getRow(2).getCell(2 + 6 * 26 + 4).setCellValue("Siso");
		FileOutputStream os;
		try {
			os = new FileOutputStream(getFile("e:/"+filePath, false));
			getBookWrite().write(os);
			os.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
